# coding=utf-8
# encoding=utf-8
import DataBase.Connect as db
import DataBase.Model as m_models
from Helper import LogHelper as log

from sqlalchemy import event
from sqlalchemy.schema import DDL


def initDB():
    #     '''
    #     1. 用DDL方式，为unique键添加自动增长
    #     2. 初始化数据库,找到BaseModel的所有子类,并在数据库中建立这些表,models里面的数据,bind绑定一个会话链接
    #     '''
    #     event.listen(
    #         m_models.ACStatus.__table__,
    #         'after_create',
    #         DDL("alter table Status_Tabel MODIFY DeviceAddress INT AUTO_INCREMENT;")
    #     )
    m_models.Base.metadata.create_all(bind=db.engine)


def insert(element):
    """
    单条插入mysql数据库
    """
    flag = False
    try:
        db.session.add(element)
        db.session.commit()
        db.session.close()
        flag = True
    except Exception as e:
        log.warn({e, '\nerror method: insert'})
        # print e, '\nerror element:'+element.getSelf()+'\t插入数据库失败,请查看主键以及唯一键约束!'
        db.session.rollback()
        db.session.flush()  # for resetting non-commited .add()
    finally:
        return flag


def insertBatch(element_list):
    """
    批量插入数据库
    """
    successNum = 0
    failNum = 0
    for element in element_list:
        if insert(element):
            successNum += 1
        else:
            failNum += 1
    return successNum, failNum


# def insertLogtBulk(self, element_list, batch_num, type):
#     '''
#     批量插入mysql数据库Log表,一次批量插入batch_num条数据
#     '''
#     mysql_batch_list = []
#     mysql_batch_num = batch_num
#     for element in element_list:
#         # 先检查在mysql中是否存在该条记录, 不存在则插入mysql
#         follow_item_exist = getattr(m_models, type).query.filter_by(id=element.id).first()
#         if not follow_item_exist:
#             mysql_batch_list.append(element)
#             mysql_batch_num -= 1
#         else:
#             # print "数据库Log表,已经存在记录:", element.getSelf()
#             log.debug(["数据库Log表,已经存在记录:", element.getSelf()])
#         # 如果mysql_batch_num 自减到0,则提交到数据库
#         if not mysql_batch_num:
#             db.session.add_all(mysql_batch_list)
#             db.session.commit()
#             mysql_batch_num = batch_num
#             mysql_batch_list = []
#     db.session.add_all(mysql_batch_list)
#     db.session.commit()

def RetWaterFirst(element):
    """
    返回根据deviceaddress查询到的第一个数据
    :param element,查询数据的deviceaddress:
    :return 查询到的模型list:
    """
    try:
        returnedData = db.session.query(m_models.ACStatusWater).filter_by(DeviceAddress=element.DeviceAddress).first()
        db.session.commit()
        db.session.close()
        return returnedData
    except Exception as e:
        # print e, '\nerror element:' + element.getSelf() + '\t查找数据库失败,请查看主键以及唯一键约束!'
        log.warn({e, '\nerror method: RetWaterFirst'})


def RetAcSendingList():
    """
    根据流水表里数据生成‘发送类’
    :return:待发送数据模型list
    """
    try:
        returnedData = db.session.query(m_models.ACStatusWater, m_models.ACInfo) \
            .filter(m_models.ACStatusWater.DeviceAddress == m_models.ACInfo.DeviceAddress).all()
        acSendlist = []
        for row in returnedData:
            acSendlist.append(m_models.ACSend(row[0], row[1]).__dict__)
        db.session.commit()
        db.session.close()
        return acSendlist
    except Exception as e:
        # print e, '\nerror element:' + '\t查找数据库失败,请查看主键以及唯一键约束!'
        log.warn({e, '\nerror method: RetAcSendingList'})


def RetStatusWater():
    """
    返回本地数据库ACStatusWater表里所有的数据
    :return ACStatusWater表里所有的数据:
    """
    try:
        returnedData = db.session.query(m_models.ACStatusWater).limit(100)
        db.session.commit()
        db.session.close()
        return returnedData
    except Exception as e:
        # print e, '\nerror element:' +'\t查找数据库失败,请查看主键以及唯一键约束!'
        log.warn({e, '\nerror method: RetStatusWater'})


def RetCrlSended():
    """
    返回ACControl表里下发执行过的数据,并将这些数据装换成ACCrlSend数据
    :return ACControl表里下发执行过并完成转换的数据:
    """
    try:
        returnedData = db.session.query(m_models.ACControl).filter(m_models.ACControl.status != u'未发送').limit(100)
        acCrlSendlist = []
        for row in returnedData:
            acCrlSendlist.append(m_models.ACCrlSend(row).__dict__)
        db.session.commit()
        db.session.close()
        return acCrlSendlist
    except Exception as e:
        db.session.rollback()
        db.session.flush()  # for resetting non-commited .add()
        # print e, '\nerror element:' +'\t查找数据库失败,请查看主键以及唯一键约束!'
        log.warn({e, '\nerror method: RetCrlSended'})


def deleteWaterData(element):
    """
    删除流水表中的信息
    :param element,将要删除的流水表的模型:
    :return:
    """
    try:
        returnedData = db.session.query(m_models.ACStatusWater).filter_by(id=element['id']).delete()
        db.session.commit()
        return returnedData
    except Exception as e:
        db.session.rollback()
        db.session.flush()  # for resetting non-commited .add()
        # print e, '\nerror element:' + element.getSelf() + '\t查找数据库失败,请查看主键以及唯一键约束!'
        log.warn([e, '\nerror element:' + '\t查找数据库失败,请查看主键以及唯一键约束!'])


def deleteSendedCrlData(element):
    """
    删除控制表中已发送的数据
    :return 查询到的将要删除的数据:
    """
    try:
        returnedData = db.session.query(m_models.ACControl).filter_by(id=element['id']).delete()
        db.session.commit()
        db.session.close()
        return returnedData
    except Exception as e:
        db.session.rollback()
        db.session.flush()  # for resetting non-commited .add()
        # print e, '\nerror element:' + element.getSelf() + '\t查找数据库失败,请查看主键以及唯一键约束!'
        log.warn({e, '\nerror method: deleteSendedCrlData'})
